﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Entity;

namespace DAL
{
    public class LayerStackService
    {
        #region 查询板材组合数据库
        public List<LayerStack> GetAll()
        {
            string sql = "select * from LayerStack";
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<LayerStack> list = new List<LayerStack>();
            while (objReader.Read())
            {
                list.Add(new LayerStack()
                {
                    LayerStackId = Convert.ToInt32(objReader["LayerStackId"]),
                    LayerStackName = Convert.ToString(objReader["LayerStackName"]),
                    Thickness1 = Convert.ToSingle(objReader["Thickness1"]),
                    Thickness2 = Convert.ToSingle(objReader["Thickness2"]),
                    Material1 = Convert.ToString(objReader["Material1"]),
                    Material2 = Convert.ToString(objReader["Material2"])
                });
            }
            objReader.Close();
            return list;
        }
        #endregion

        #region 判断当前ID号是否已在数据库存在
        /// <summary>
        /// 判断当前ID号是否已在数据库存在
        /// </summary>
        /// <param name="LayerStackId"></param>
        /// <returns></returns>
        public bool IsIdNoExisted(string LayerStackId)
        {
            string sql = "select count(*) from LayerStack where LayerStackId ={0}";
            sql = string.Format(sql, LayerStackId);
            int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
            if (result == 1) return true;
            else return false;
        }
        #endregion

        #region 添加板材组合
        /// <summary>
        /// 添加板材组合对象信息到数据库
        /// </summary>
        /// <param name="objLayerStack"></param>
        /// <returns></returns>
        public int AddLayerStack(LayerStack objLayerStack)
        {
            StringBuilder objBuilder = new StringBuilder();
            objBuilder.Append("insert into LayerStack(LayerStackName,LayerStackId,Thickness1,Thickness2,Material1,Material2)");
            objBuilder.Append(" values('{0}',{1},{2},'{3}','{4}','{5}')");
            string sql = string.Format(objBuilder.ToString(),
                objLayerStack.LayerStackName, objLayerStack.LayerStackId, objLayerStack.LayerStackId, objLayerStack.Thickness1,
                objLayerStack.Thickness2, objLayerStack.Material1, objLayerStack.Material2);
            //提交到数据库
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 更新板材组合,不可更改编号
        /// <summary>
        /// 更新板材组合信息到数据库
        /// </summary>
        /// <param name="objLayerStack"></param>
        /// <returns></returns>
        public int ModifyLayerStack(LayerStack objLayerStack)
        {
            StringBuilder objBuilder = new StringBuilder();
            objBuilder.Append("Update LayerStack set LayerStackName='{0}',LayerStackId={1},Thickness1={2},Thickness2={3},Material1='{4}',Material2='{5}'");
            objBuilder.Append(" where LayerStackId={6}");
            string sql = string.Format(objBuilder.ToString(),
                objLayerStack.LayerStackName, objLayerStack.LayerStackId, objLayerStack.Thickness1,
                objLayerStack.Thickness2, objLayerStack.Material1, objLayerStack.Material2, objLayerStack.EidtId);
            //提交到数据库
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 删除板材组合
        /// <summary>
        /// 删除板材组合信息到数据库
        /// </summary>
        /// <param name="layerStackId"></param>
        /// <returns></returns>
        public int DeleteLayerStackById(string layerStackId)
        {
            string sql = "delete from LayerStack where LayerStackId=" + layerStackId;
            //提交到数据库
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (SqlException ex)
            {
                if (ex.Number == 547)
                    throw new Exception("该板材组合被其他数据表引用，不能直接删除该板材组合对象！");
                else
                    throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion























        #region 判断当前ID号是否已在数据库存在
        /// <summary>
        /// 判断当前ID号是否已在数据库存在
        /// </summary>
        /// <param name="LayerStackId"></param>
        /// <returns></returns>
        public bool IsNewLayerStackNameExisted(string LayerStackId)
        {
            string sql = "select count(*) from LayerStack where LayerStackName ='{0}'";
            sql = string.Format(sql, LayerStackId);
            int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
            if (result == 1) return true;
            else return false;
        }
        #endregion

        #region 添加板材组合
        /// <summary>
        /// 添加板材组合对象信息到数据库
        /// </summary>
        /// <param name="objLayerStack"></param>
        /// <returns></returns>
        public int AddNewLayerStackName(LayerStack objLayerStack)
        {
            StringBuilder objBuilder = new StringBuilder();
            objBuilder.Append("insert into LayerStack(LayerStackName,LayerStackId,Thickness1,Thickness2,Material1,Material2)");
            objBuilder.Append(" values('{0}',{1},{2},{3},'{4}','{5}')");
            string sql = string.Format(objBuilder.ToString(),
                objLayerStack.LayerStackName, objLayerStack.LayerStackId,  objLayerStack.Thickness1,
                objLayerStack.Thickness2, objLayerStack.Material1, objLayerStack.Material2);
            //提交到数据库
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 更新板材组合,不可更改编号
        /// <summary>
        /// 更新板材组合信息到数据库
        /// </summary>
        /// <param name="objLayerStack"></param>
        /// <returns></returns>
        public int ModifyNewLayerStackName(LayerStack objLayerStack)
        {
            StringBuilder objBuilder = new StringBuilder();
            objBuilder.Append("Update LayerStack set  Thickness1={1},Thickness2={2},Material1='{3}',Material2='{4}'");
            objBuilder.Append(" where LayerStackName='{0}'");
            string sql = string.Format(objBuilder.ToString(),
                objLayerStack.LayerStackName,   objLayerStack.Thickness1,
                objLayerStack.Thickness2, objLayerStack.Material1, objLayerStack.Material2);
            //提交到数据库
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 删除板材组合
        /// <summary>
        /// 删除板材组合信息到数据库
        /// </summary>
        /// <param name="layerStackId"></param>
        /// <returns></returns>
        public int DeleteLayerStackByName(string layerStackId)
        {
            string sql = "delete from LayerStack where LayerStackId=" + layerStackId;
            //提交到数据库
            try
            {
                return SQLHelper.Update(sql);
            }
            catch (SqlException ex)
            {
                if (ex.Number == 547)
                    throw new Exception("该板材组合被其他数据表引用，不能直接删除该板材组合对象！");
                else
                    throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}
